#! /bin/bash

HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e "


create database  if not exists oe_dm;
use oe_dm;
drop table if exists oe_dm.dm_entry_rollup;
create table oe_dm.dm_entry_rollup(

      year string,   --年
      month string,  -- 月
      day string,   --日
      origin_type string,   --线上线下
      itcast_school_id string,
      itcast_subject_id string,
      origin_channel string,  --来源渠道
      consult_center string,  --咨询中心
      group_type string,  --分组标记
      time_type string,   --时间标记
      report_num bigint,   --报名人数
      intent_num bigint,   -- 意向人数
      clue_num bigint  -- 有效线索人数

)COMMENT '报名主题上卷'
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');
"

PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "

create database  if not exists dm;
use dm;

-- 日--->月--->进行上卷
insert into hive.oe_dm.dm_entry_rollup
select
      year,
      month,
      day,
      origin_type,
      itcast_school_id,
      itcast_subject_id,
      origin_channel,
      consult_center,  -- 报名人数判断
      case when grouping(itcast_school_id) = 0 and grouping(itcast_subject_id) = 0 then 'school_subject'
         --  when grouping(origin_type) =1 and grouping(itcast_school_id) = 0 then 'no_origin_school'
           when grouping(itcast_subject_id) = 0 then 'subject'
           when grouping(origin_type) = 1 then 'no_origin_school'
           when grouping(itcast_school_id) = 0  then 'school'
           when grouping(origin_channel) = 0 then 'channel'
           when grouping(consult_center) = 0 then 'consult_center'
           when grouping(origin_type) = 0 then 'origin_type'
           else 'other' end as group_type,
      case when grouping(day) = 0 then 'date'
           when grouping(month) = 0 then 'month'
           when grouping(year) = 0 then 'year'
           else 'other' end as time_type,
           --else 'no_origin_school' end as group_type,
              sum(report_num) as report_num,
              sum(intent_num) as intent_num,
              sum(clue_num) as clue_num
from hive.oe_dws.dws_entry_daycount
group by
grouping sets (
-- 年
    (year,itcast_school_id),
    (year,origin_type,itcast_school_id),
    (year,origin_type,itcast_subject_id),
    (year,origin_type,itcast_school_id,itcast_subject_id),
    (year,origin_type,origin_channel),
    (year,origin_type,consult_center),
    (year,origin_type),
    --月
    (year,month,itcast_school_id),
    (year,month,origin_type,itcast_school_id),
    (year,month,origin_type,itcast_subject_id),
    (year,month,origin_type,itcast_school_id,itcast_subject_id),
    (year,month,origin_type,origin_channel),
    (year,month,origin_type,consult_center),
    (year,month,origin_type),
     --日
    (year,month,day,itcast_school_id),
    (year,month,day,origin_type,itcast_school_id),
    (year,month,day,origin_type,itcast_subject_id),
    (year,month,day,origin_type,itcast_school_id,itcast_subject_id),
    (year,month,day,origin_type,origin_channel),
    (year,month,day,origin_type,consult_center),
    (year,month,day,origin_type)
);
"